﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using BOM.entity;

namespace BOM.dao
{
    class PriceBillEntryFormNew
    {
        //查询表materialgroup的所有信息
        public static DataTable SelectMaterialgroup()
        {
            string cmdText = "select fname from materialgroup";
            DataTable table = DbService.getSQLTable(cmdText, "materialgroup");
            if (table.Rows.Count > 0)
            {
                return table;
            }
            else
            {
                return null;
            }
        }
        //根据fname 查询表material的数据
        public static DataTable SelectfnameMaterialfid(string fname)
        {
            string cmdText = "select * from materialgroup where fname = '" + fname + "'";
            DataTable table = DbService.getSQLTable(cmdText,"materialgroup");
            if (table.Rows.Count > 0)
            {
                return table;
            }
            else
            {
                return null;
            }
        }
        //根据fid查询表material里面的fname
        public static DataTable Selectfidmaterial(int fid)
        {
            string cmdText = "select * from material where fgroupid = " + fid + "";
            DataTable table = DbService.getSQLTable(cmdText, "material");
            if (table.Rows.Count > 0)
            {
                return table;
            }
            else
            {
                return null;
            }
        }
        //根据材料名和材料分类查询数据
        public static DataTable SelectmaterialMaterialgroup(string material, string materialgroup)
        { 
            DataTable table = SelectfnameMaterialfid(materialgroup);
            int fid=0;
            if(table.Rows.Count==1)
            {
                fid = Convert.ToInt32(table.Rows[0]["fid"].ToString());
            }
            string cmdText = "select * from material where fname = '" + material + "' and fgroupid = " + fid + "";
            DataTable mtable = DbService.getSQLTable(cmdText,"material");
            if (mtable.Rows.Count == 1)
            {
                return mtable;
            }
            else
            {
                return null;
            }
        }
        //根据数量、规格、单价查询表 pricebillentry的数据取出唯一的ID
        public static DataTable SelectMateriGroupsFid(string fqty, string fprie, int fparentid)
        {
            string cmdText = "select pricebillentry.fid,material.*, materialgroup.* from pricebillentry,material,materialgroup where pricebillentry.fmaterialid = material.fid and material.fgroupid = materialgroup.fid and pricebillentry.fprice = " + fprie + " and fqty = " + fqty + " and pricebillentry.fparentid = " + fparentid + "";
            DataTable table = DbService.getSQLTable(cmdText, "MateriGroupsFid");
            if (table.Rows.Count == 1)
            {
                return table;
            }
            else
            {
                return null;
            }
        }
        //修改表pricebillentry中的数据
        public static bool UpdatePricebillEntry(int fid)
        { 
            TemporaryValue value = new TemporaryValue();
            string cmdText = "update pricebillentry set fmaterialid = " + value.Fmaterialid + ",fsize = '" + value.Fsize + "',funit ='" + value.Funit + "',fqty = " + value.Fqty + ",famount = " + value.Famout + ",fdescription = '" + value.Fdescription +
                "',fprice = " + value.Fpric + " where fid = " + fid + "";
            int i = DbService.executeSQL(cmdText);
            if (i > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

    }
}
